/*
This file is part of Cafca.

Cafca is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Cafca is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Cafca.  If not, see <http://www.gnu.org/licenses/>.

If you have any comment, suggestion or idea, do not hesitate to contact me at shenrotin@gmail.com
*/

package org.crag.cafca.db;

/**
 * Some usefull tools to use with a DB and some tools to manage users in DB. 
 * 
 */
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.log4j.Logger;
import org.crag.cafca.beans.User;
import org.crag.cafca.logic.time.Event;
import org.crag.cafca.logic.time.FamilyEvent;
import org.crag.cafca.logic.time.SourceEvent;
import org.crag.cafca.logic.time.SourceManagementEvent;
import org.crag.cafca.logic.time.Tag;
import org.crag.cafca.logic.time.TimeLine;
import org.crag.cafca.util.Utilities;
import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import com.mysql.jdbc.Driver;

public class SqlConnector 
{
	/**
	 * The MySql driver
	 */
	private static Driver driver;
	
	/**
	 * Logger log4j. See log4j doc for more information.
	 */
	private static Logger log=Logger.getLogger(new Throwable().getStackTrace()[0].getClassName());

	/**
	 * The connection to the database
	 */
	private Connection connection;
	
	private static String host;
	private static String port;
	private static String database;
	private static String user;
	private static String password;
	
	/**
	 * This method should be called only once and initialise the pooling system. You don't have to call it each time you want a SQL connection
	 * @param host ip of dns of the server hosting the database
	 * @param port port used to connect
	 * @param database database or schema
	 * @param user user for the db
	 * @param password password for the db
	 */
	public static void init(String host,String port,String database,String user,String password)
	{					
		SqlConnector.host=host;
		SqlConnector.port=port;
		SqlConnector.database=database;
		SqlConnector.user=user;
		SqlConnector.password=password;
		
		try 
        {
			Class.forName("com.mysql.jdbc.Driver");
		} 
        catch (ClassNotFoundException e) 
        {
			e.printStackTrace();
		}
        

	}
	
	public static void destroy()
	{
		log.debug("Destroying the SqlConnector");
		try 
		{
			DriverManager.deregisterDriver(driver);
		} 
		catch (SQLException e) 
		{
			log.error("Failed to unregister the MySql driver");
		}
	}
	
	public SqlConnector()
	{
	    log.info("Creating a sql connection");
	    try 
		{
			connection = DriverManager.getConnection("jdbc:mysql://"+host+"/"+database+"?user="+user+"&password="+password);
		} 
		catch (SQLException e) 
		{
			log.error("Cannot connect to the database: "+e.getMessage());
		} 
		catch (Exception e) 
		{
			log.error("Cannot connect to the database: "+e.getMessage());
		}
	}
	
	/**
	 * Execute an SQL query. The caller is supposed to provide the Statement. Use this method when you expect a result from the db.(SELECT, etc...)
	 * @param statement The Statement. @see java.sql.Statement
	 * @param query the Sql query
	 * @return The result of the query.
	 */
	private ResultSet executeQuery(Statement statement, String query)
	{
		ResultSet result = null;
		
		if (statement!=null)
		{
			log.debug("SQL Query : "+query);
			try {
				result=statement.executeQuery(query);
			} 
			catch (SQLException e) 
			{
				log.error("Error with the query : "+e.getMessage()+". Sql state : "+e.getSQLState());
			}
		} 
		else
		{
			log.error("Trying to execute the query : "+query+", but the statement is null");
		}

		return result;
	}
	
	private long executeUpdate(Statement statement, String query){
		
		long result = -1; 
		
		if (statement!=null)
		{
			log.debug("SQL Query : "+query);
			try 
			{
				statement.executeUpdate(query,Statement.RETURN_GENERATED_KEYS); 
				ResultSet generatedKeysResultSet= statement.getGeneratedKeys();
				if (generatedKeysResultSet != null && generatedKeysResultSet.next()) 
				{
				    result= generatedKeysResultSet.getLong(1);
				}
			} 
			catch (SQLException e) 
			{
				log.error("Error with the query : "+e.getMessage()+". Sql state : "+e.getSQLState());
			}
		} 
		else
		{
			log.error("Trying to execute the query : "+query+", but the statement is null");
		}
		
		return result;
	}
	
	private Statement createStatement()
	{
		Statement st = null; 
		try 
		{
			st = connection.createStatement();
		} 
		catch (SQLException e) 
		{
			log.error("Impossible to create a statement: "+e.getMessage());
		}
		catch (NullPointerException e)
		{
			log.error("Impossible to create a statement: "+e.getMessage());			
		}
		
		return st;
	}
	
	private void closeStatement(Statement statement)
	{
		try 
		{
			statement.close();
		} 
		catch (SQLException e) 
		{
			log.error("Impossible to close a statement");
		}
		catch (NullPointerException e)
		{
			log.error("Impossible to close a statement");			
		}
	}
	
	public boolean doesUsernameExist(String username) 
	{
		log.debug("Checking if the user: "+username+" exists");
		boolean result = false; 
		Statement statement = createStatement(); 
		ResultSet resultSet = null; 
		String query = "SELECT username FROM users WHERE username='"+username+"'"; 
		resultSet = executeQuery(statement, query); 
		try 
		{
			if(resultSet.first())
				result = true; 
			else
				result = false;
		} 
		catch (SQLException e) 
		{
			log.warn("Error with the data when checking of the user "+username+" exists."+e.getMessage());
			closeStatement(statement);
			return false;
		}
		
		closeStatement(statement);
		return result; 
	}

	public void insertUser(User user) 
	{
		Statement ps = createStatement(); 
		String query = "INSERT INTO users(name, email, username, password, inscriptionDate) VALUES('"+user.getName()+"', '"+user.getEmail()+"', '"+user.getUsername()+"', sha1('"+user.getPassword()+"'), NOW())";  
		executeUpdate(ps, query); 
		closeStatement(ps);
	}

	/**
	 * Close the connection with the database. Don't forget to call it because the pool will run out of connection really quick.
	 */	
	public void close()
	{
		log.info("Closing the SQL Connection");
		try 
		{
			connection.close();
		} 
		catch (SQLException e) 
		{
			log.warn("Error when closing the sql connection");
		}
	}
	
	

	public void saveNewEvent(Event event)
	{
		Statement statement = createStatement();

		String query=null;
		
		int eventTypeId=getEventTypeId(statement,event.getClass().getName());
		
		if (event instanceof SourceEvent)
		{
			SourceEvent sourceEvent=(SourceEvent)event;
			if (event.getEndDateTime()!=null)
			{
				query = "INSERT INTO event(familyId, sourceId, startDateTime, endDateTime, description, value, tags, eventType) VALUES('"+event.getTimeLine().getFamilyName()+"', '"+sourceEvent.getSourceId()+"', '"+Utilities.dateTimeToSQL(sourceEvent.getStartDateTime())+"', '"+Utilities.dateTimeToSQL(sourceEvent.getEndDateTime())+"', '"+sourceEvent.getDescription()+"', '"+event.getValue()+"' ,'"+sourceEvent.getTagString()+"',"+eventTypeId+")";				
			}
			else
			{
				query = "INSERT INTO event(familyId, sourceId, startDateTime, description, value, tags, eventType) VALUES('"+event.getTimeLine().getFamilyName()+"', '"+sourceEvent.getSourceId()+"', '"+Utilities.dateTimeToSQL(sourceEvent.getStartDateTime())+"', '"+sourceEvent.getDescription()+"', '"+event.getValue()+"' ,'"+sourceEvent.getTagString()+"',"+eventTypeId+")";								
			}
		}

		if (event instanceof SourceManagementEvent)
		{
			SourceManagementEvent sourceEvent=(SourceManagementEvent)event;
			if (event.getEndDateTime()!=null)
			{
				query = "INSERT INTO event(familyId, sourceId, startDateTime, endDateTime, description, value, tags, eventType) VALUES('"+event.getTimeLine().getFamilyName()+"', '"+sourceEvent.getSourceId()+"', '"+Utilities.dateTimeToSQL(sourceEvent.getStartDateTime())+"', '"+Utilities.dateTimeToSQL(sourceEvent.getEndDateTime())+"', '"+sourceEvent.getDescription()+"', '"+event.getValue()+"' ,'"+sourceEvent.getTagString()+"',"+eventTypeId+")";
			}
			else
			{
				query = "INSERT INTO event(familyId, sourceId, startDateTime, description, value, tags, eventType) VALUES('"+event.getTimeLine().getFamilyName()+"', '"+sourceEvent.getSourceId()+"', '"+Utilities.dateTimeToSQL(sourceEvent.getStartDateTime())+"', '"+sourceEvent.getDescription()+"', '"+event.getValue()+"' ,'"+sourceEvent.getTagString()+"',"+eventTypeId+")";
			}
		}
		
		if (event instanceof FamilyEvent)
		{
			if (event.getEndDateTime()!=null)
			{
				query = "INSERT INTO event(familyId, startDateTime, endDateTime, description, value, tags, eventType) VALUES('"+event.getTimeLine().getFamilyName()+"', '"+Utilities.dateTimeToSQL(event.getStartDateTime())+"', '"+Utilities.dateTimeToSQL(event.getEndDateTime())+"', '"+event.getDescription()+"', '"+event.getValue()+"' ,'"+event.getTagString()+"',"+eventTypeId+")";				
			}
			else
			{
				query = "INSERT INTO event(familyId, sourceId, startDateTime, description, value, tags, eventType) VALUES('"+event.getTimeLine().getFamilyName()+"', '"+Utilities.dateTimeToSQL(event.getStartDateTime())+"', '"+event.getDescription()+"', '"+event.getValue()+"' ,'"+event.getTagString()+"',"+eventTypeId+")";
			}
		}
				
		long eventId=executeUpdate(statement, query); 
		event.setId(eventId);
		
		closeStatement(statement);
	}


	private int getEventTypeId(Statement statement,String className) 
	{
		String query="SELECT id from cafca.eventtype WHERE eventtype.class='"+className+"'";
		ResultSet resultSet=executeQuery(statement, query);
		
		int result=0;
		
		try 
		{
			if (resultSet.first()==false)
			{
				log.warn("Unknown eventType: "+className);
				result=-1;
			}
			else
			{
				result=resultSet.getInt("id");
			}
		} 
		catch (SQLException e) 
		{
			log.warn("Error with the database when looking for the eventTypeId of the eventType: "+className+", "+e.getMessage());
			return -1;
		}
		
		return result;
	}

	public boolean checkUsernameAndPassword(String username, String password) 
	{
		boolean result = false; 

		log.debug("Checking the password of the user: "+username);
		Statement statement = createStatement(); 
		ResultSet resultSet = null; 
		String query = "SELECT username FROM users WHERE username='"+username+"' AND password=sha1('"+password+"')"; 
		resultSet = executeQuery(statement, query); 
		try 
		{
			if(resultSet.first())
				result = true; 
			else
				result = false;
		} 
		catch (SQLException e) 
		{
			log.warn("Error with the database when checking the password of the user "+username+"."+e.getMessage());
			closeStatement(statement);
			return false;
		}
		
		closeStatement(statement);
		return result; 	
	}

	public TimeLine loadTimeLine(String username) 
	{
		log.debug("Loading the timeline for the family:"+username);
		Statement statement = createStatement();
		
		TimeLine result=new TimeLine(username);
		
		String query = "SELECT event.id, event.sourceId, event.description, event.startDateTime, event.endDateTime, event.value, event.tags, eventType.name FROM cafca.event,cafca.eventType WHERE event.familyId='"+username+"' AND event.eventType=eventType.id"; 
		
		ResultSet resultSet = executeQuery(statement, query); 
		
		try 
		{
			while(resultSet.next())
			{
				int id=resultSet.getInt("id");
				String sourceId=resultSet.getString("sourceId");
				String description=resultSet.getString("description");
				Timestamp startTimestamp=resultSet.getTimestamp("startDateTime");
				Timestamp endTimestamp=resultSet.getTimestamp("endDateTime");				
				String value=resultSet.getString("value");
				String eventType=resultSet.getString("eventType.name");
				
				String tags=resultSet.getString("tags");
				
				Event event=null;
				
				if (eventType.equals("SourceEvent"))
				{
					event=new SourceEvent(sourceId);
				}
				
				if (eventType.equals("FamilyEvent"))
				{
					event=new FamilyEvent();
				}

				if (eventType.equals("SourceManagementEvent"))
				{
					event=new SourceManagementEvent(sourceId);					
				}
				
				event.setDescription(description);

				if (tags!=null)
					event.addTags(tags);

				if (startTimestamp!=null)
					event.setStartDateTime(new DateTime(startTimestamp, DateTimeZone.forID("UTC")));
				
				if (endTimestamp!=null)
					event.setEndDateTime(new DateTime(endTimestamp, DateTimeZone.forID("UTC")));
				
				if (value!=null)
					event.setValue(value);
				
				event.setId(id);
				
				result.addEvent(event);
			}	
		} 
		catch (SQLException e) 
		{
			log.warn("Error when fetching the events friom the database. "+e.getMessage());
		} 
		
		closeStatement(statement);
		
		return result;
	}

	public void updateEvent(Event event) 
	{
		log.debug("Updating event "+event+" int the database");

		Statement statement = createStatement();

		String query = "UPDATE event SET " +
				"startDateTime='"+Utilities.dateTimeToSQL(event.getStartDateTime().toDateTime(DateTimeZone.UTC))+"', " +
				((event.getEndDateTime()!=null)?("endDateTime='"+Utilities.dateTimeToSQL(event.getEndDateTime().toDateTime(DateTimeZone.UTC))+"', "):("")) +
				"description='"+event.getDescription()+"', " +
				"value='"+event.getValue()+"', " +
				"tags='"+event.getTagString()+"' " +
				"WHERE id="+event.getId();					
						
		executeUpdate(statement, query); 
		closeStatement(statement);
	}
	
	public void deleteEvent(Event event)
	{
		log.debug("Deleting event "+event+" from the database");
		String query="DELETE FROM event WHERE id="+event.getId();
		Statement statement = createStatement();
		executeUpdate(statement, query); 
		closeStatement(statement);
	}
}
